package tw.com.softleader.reins.demo.city.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import tw.com.softleader.reins.demo.city.entity.City;
@Repository
public class CityDao {
	
	@Autowired
	private DataSource dataSource;
	
	static private final String SELECT_ALL="SELECT * FROM city";
	public List<City> getAll(){
		
		List<City> result = new ArrayList<City>();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(SELECT_ALL);
			rs = pstmt.executeQuery();
			
			City city = null;
			while(rs.next()){
				city = new City();
				city.setId(rs.getLong("id"));
				city.setName(rs.getString("name"));
				city.setPopulation(rs.getInt("population"));
				city.setArea(rs.getDouble("area"));
				result.add(city);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if (pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if (conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
		}
		return result;
	} 
	
	static private final String SELECT_BY_ID="SELECT * FROM city WHERE id = ?";
	public City getById(Long id){
		
		City city = new City();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(SELECT_BY_ID);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			
			rs.next();
			city.setId(rs.getLong("id"));
			city.setName(rs.getString("name"));
			city.setPopulation(rs.getInt("population"));
			city.setArea(rs.getDouble("area"));
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if (pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if (conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
		}
		return city;
	}
	
	static private final String INSERT="INSERT INTO city(id,name,population,area) VALUES(?,?,?,?) ";
	
	public Integer insert(City city){
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(INSERT);
			pstmt.setLong(1, city.getId());
			pstmt.setString(2, city.getName());
			pstmt.setInt(3, city.getPopulation());
			pstmt.setDouble(4, city.getArea());
			
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	static private final String UPDATE="UPDATE city SET name=?, population=?, area=? WHERE id = ?";

	public Integer update(City city){
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(UPDATE);
			pstmt.setString(1, city.getName());
			pstmt.setInt(2, city.getPopulation());
			pstmt.setDouble(3, city.getArea());
			pstmt.setLong(4, city.getId());
			
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	static private final String DELETE="DELETE FROM city WHERE id = ?";

	public Integer delete(City city){
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(DELETE);
			pstmt.setLong(1, city.getId());
			
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			if(pstmt!=null){
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
